﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Security.Cryptography;
using System.Text;
using System.Configuration;
using System.IO;
using log4net;
using System.Reflection;

namespace H2.NET
{
    public class TaskDB : IDisposable
    {
        #region 私有变量定义
        private OleDbConnection oleCnn;
        private OleDbCommand oleCmm;
        private OleDbTransaction oleTra;
        private string strConn;   /* 数据库联接字符串 */
        #endregion

        #region 类构造clsOleDB
        public TaskDB()
        {

        }
        #endregion

        #region 设置数据库连接字符串
        /// <summary>
        /// 设置数据库连接字符串
        /// <param name="strConn"></param>
        public void Storage(string Storage)
        {
            this.strConn = Util.AppSettings("crypt").ToLower() == "yes" ? Util.Decrypt(Util.ConnectionStrings(Storage)) : Util.ConnectionStrings(Storage);
        }
        #endregion

        #region 数据库连接打开
        /// <summary>
        /// 数据库连接打开
        /// </summary>
        private void oleOpen()
        {
            oleCnn = new OleDbConnection(strConn);
            try
            {
                oleCnn.Open();
            }
            catch (Exception)
            {
                oleCnn.Close();
            }
        }
        #endregion

        #region 根据SQL查询返回 (dt)
        /// <summary>
        /// 根据SQL查询返回 (dt)
        /// </summary>
        public DataTable Query(string Sql)
        {
            oleOpen();
            OleDbDataAdapter oleDa = new OleDbDataAdapter(Sql, oleCnn);
            DataSet dsRtn = new DataSet();
            DataTable dtRtn = new DataTable();
            oleDa.Fill(dsRtn);

            //根据查询的结果返回具体的 (dt)
            if (dsRtn != null)
            {
                if (dsRtn.Tables.Count > 0)
                {
                    dtRtn = dsRtn.Tables[0];
                }
                else
                {
                    dtRtn = null;
                }
            }
            else
            {
                dtRtn = null;
            }
            //数据库连接关闭
            oleClose();

            //具体值返回
            return dtRtn;
        }
        #endregion

        #region 根据SQL返回首行首列
        /// <summary>
        /// 根据SQL返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetSingle(string sql)
        {
            oleOpen();
            oleCmm = new OleDbCommand(sql, oleCnn);
            object result = oleCmm.ExecuteScalar();
            oleClose();
            return result;
        }
        #endregion

        #region 执行SQL语句 (返回影响行数)
        /// <summary>
        /// 执行SQL语句 (返回影响行数)
        /// </summary>
        public int ExcuteSql(string Sql)
        {
            oleOpen();
            oleCmm = new OleDbCommand(Sql, oleCnn);
            int intNum = oleCmm.ExecuteNonQuery();
            oleClose();
            return intNum;
        }
        #endregion

        #region 数据库事务开始
        /// <summary>
        /// 数据库事务开始
        /// </summary>
        public void BeginTransaction()
        {
            oleOpen();
            oleTra = oleCnn.BeginTransaction();
        }
        #endregion

        #region 数据库事务提交
        /// <summary>
        /// 数据库事务提交
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public void Commit()
        {
            oleTra.Commit();
            oleClose();
        }
        #endregion

        #region 数据库事务回滚
        /// <summary>
        /// 数据库事务回滚
        /// </summary>
        public void Rollback()
        {
            oleTra.Rollback();
            oleClose();
        }
        #endregion

        #region 执行SQL语句-事务 (返回影响行数)
        /// <summary>
        /// 执行SQL语句-事务 (返回影响行数)
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public int ExcuteSqlWithTransaction(string Sql)
        {
            //如果OleDbCommand为空是初始化
            oleCmm = new OleDbCommand(Sql, oleCnn);
            oleCmm.Transaction = oleTra;
            oleCmm.CommandText = Sql;

            int intNum = oleCmm.ExecuteNonQuery();
            return intNum;
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public int ExcuteStoredProcedure(string Procedure, string[] Name, string[] Value)
        {
            oleOpen();
            oleCmm = new OleDbCommand(Procedure, oleCnn);
            oleCmm.CommandType = CommandType.StoredProcedure;
            OleDbParameter[] oleDbParam = new OleDbParameter[Name.Length];

            //传递参数
            for (int i = 0; i < oleDbParam.Length; i++)
            {
                oleDbParam[i] = new OleDbParameter(Name[i], OleDbType.VarChar, 600);
                oleDbParam[i].Value = Value[i].Trim();
                oleCmm.Parameters.Add(oleDbParam[i]);
            }

            //返回结果
            int intNum = oleCmm.ExecuteNonQuery();
            oleClose();
            return intNum;
        }
        #endregion

        #region 数据库连接关闭
        /// <summary>
        /// 数据库连接关闭
        /// </summary>
        private void oleClose()
        {
            oleCnn.Close();
            oleCnn.Dispose();
        }
        #endregion

        #region 日志记录函数
        /// <summary>
        /// 日志记录函数
        /// </summary>
        /// <param name="strPath"></param>
        /// <param name="strInfo"></param>
        public static void Log(string strPath, bool booDebug, string strInfo)
        {
            try
            {
                //初始化写是否写日志标志变量
                bool booFlg = true;

                //根据不同的情况改变是否写日志标志变量的值
                if (!booDebug)
                {
                    booFlg = true;
                }
                else
                {
                    if (Util.AppSettings("debug").ToLower() == "yes")
                    {
                        booFlg = true;
                    }
                    else
                    {
                        booFlg = false;
                    }
                }

                //根据是否写日志标志变量的取值,来执行日志写入
                if (booFlg)
                {
                    Logging.GetLogger().Debug(strInfo);
                }
            }
            catch
            {
            }
        }


        public static void LogInfo(string strInfo)
        {
            Logging.GetLogger().Info(strInfo);
        }
        #endregion

        #region IDisposable 成员
        /// <summary>
        /// 数据库对象的销毁
        /// </summary>
        public void Dispose()
        {
            try
            {
                oleCnn.Dispose();
                oleCmm.Dispose();
                oleTra.Dispose();
                strConn = null;
            }
            catch (Exception)
            {
            }
        }
        #endregion
    }
}

